//
//  MYSQLLiteTableiabe+HandyJSON.swift
//  MYModule
//
//  Created by 尹琼 on 2022/11/7.
//

import UIKit
import HandyJSON
import SQLite
import SwiftDate
import MYModule

public extension MYSQLiteTableiabe where Self: HandyJSON {
    
    static func isEmpty(mac: String? = DeviceInfo.current().adress) -> Bool? {
        
        guard let _ = first() else {
            return false
        }
        return true
        
    }
    
    static func first(mac: String? = DeviceInfo.current().adress) ->Self? {
        
       
        
        let all = [Self].all(mac: mac).flatMap { $0 }
        return all.first
    }
    
    static func last(mac: String? = DeviceInfo.current().adress) ->Self? {
        
       
        
        let all = [Self].all(mac: mac).flatMap { $0 }
        return all.last
        
    }
    
}


public extension Array where Element: (HandyJSON & MYSQLiteTableiabe) {
    
    //    /// 分段查询models
    //    /// - Parameters:
    //    ///   - limmit: limmit
    //    ///   - offset: offset
    //    /// - Returns: models
    //    static func deserialize(offset: Int, limmit: Int) ->[Element?]? {
    //
    //        let query = Element.table.limit(limmit, offset: offset)
    //
    //        guard let rows = try? Element.db.prepare(query) else {
    //            return nil
    //        }
    //
    //        let models = deserialize(from: rows)
    //        return models
    //
    //    }
    
    /// 根据年月日查询
    /// - Parameters:
    ///   - year: 年
    ///   - month: 月
    ///   - day: 日
    ///   - limmit: limmit
    ///   - offset: offset
    /// - Returns: models
    static func deserialize(mac: String? = DeviceInfo.current().adress, year: Int, month: Int = 0, day: Int = 0, limmit: Int = 0, offset: Int = 1) ->[Element]? {
        
        
        var query = Element.table
        
        if year > 0 && month > 0 && day > 0  {
            
            let locaDate = Date(year: year, month: month, day: day, hour: 0, minute: 0)
            query = query.where(Element.expression_localDate == locaDate)
            
        } else {
            
            var query = Element.table.where(Element.expression_year == year)
            
            if month > 0 {
                query = query.where(Element.expression_month == month)
            }
            if day > 0 {
                query = query.where(Element.expression_day == day)
            }
        }
        
        if limmit > 0 && offset > 0 {
            query = query.limit(limmit, offset: offset)
        }
        
        guard let rows = try? Element.db.prepare(query) else {
            return nil
        }
        
        
        let models = deserialize(from: rows)
        return models
        
    }
    
    /// 查询一个月多少天之间的数据
    /// - Parameters:
    ///   - year: 年
    ///   - month: 月
    ///   - offsetDay: 开始天
    ///   - limmitDay: 从开始天之后多少天
    /// - Returns: models
    static func deserialize(mac: String? = DeviceInfo.current().adress, year: Int, month: Int, offsetDay: Int = 0, limmitDay: Int = 31) ->[Element?]? {
        
       
//        Element.expression_mac == mac &&
        var query = Element.table.where(Element.expression_year == year)
        if month > 0 {
            query = query.where(Element.expression_month == month)
        }
        
        query = query.where(Element.expression_day > offsetDay).where(Element.expression_day <= (offsetDay + limmitDay))
        
        
        guard let rows = try? Element.db.prepare(query) else { return nil }
        let models = [Element].deserialize(from: rows)
        return models
        
    }
    
    static func deserialize(mac: String? = DeviceInfo.current().adress, date: Date, offsetDay: Int = 0, limmitDay: Int = 31) ->[Element?]? {
        
        return deserialize(mac: mac, year: date.year, month: date.month, offsetDay: offsetDay, limmitDay: limmitDay)
        
    }
    
    /// 查询多少天之间的数据（支持跨年月，一个数组）
    /// - Parameters:
    ///   - date: 开始日期
    ///   - range: 区间
    /// - Returns: models
    static func deserialize(mac: String? = DeviceInfo.current().adress, allModelOf date: Date, range: Range<Int>) ->[Element] {
        
        let rangeModels = deserialize(mac: mac, from: date, range: range)
        let models = rangeModels.flatMap { $0 }
        return models
    }
    
    
    /// 查询多少天之间的数据(支持跨年月, 每一天数据都一个数组)
    /// 注意： 未查到返回的是一个空数组
    /// - Parameters:
    ///   - date: 开始的日期
    ///   - range: 区间
    /// - Returns: [models]
    static func deserialize(mac: String? = DeviceInfo.current().adress, from date: Date, range: Range<Int>) ->[[Element]] {
        
        return range.map {
            let fromDate = date + $0.days
            let model = [Element].deserialize(mac: mac, from: fromDate) ?? []
            
            return model
        }
    }
    
    
    /// 查询一天的数据(一个数组)
    /// - Parameter date: 日期
    /// - Returns: []
    static func deserialize(mac: String? = DeviceInfo.current().adress, from date: Date) ->[Element]? {
        let models = [Element].deserialize(mac: mac, year: date.year, month: date.month, day: date.day)
        return models
        
    }
    
    /// 查询 24小时数据(一天一个数组)
    /// - Parameter date: 开始日期
    /// - Returns: [[]]
    static func deserialize(mac: String? = DeviceInfo.current().adress, hoursFor24 fromDate: Date) ->[[Element]] {
        
        let range = -1 ..< 0
        let models = deserialize(mac: mac, from: fromDate, range: range)
        return models
        
    }
    
    /// 查询 一周的数据（一天一个数组）
    /// - Parameter fromDate: 开始日期
    /// - Returns: [[]]
    static func deserialize(mac: String? = DeviceInfo.current().adress, weekDays fromDate: Date) ->[[Element]] {
        
        let range = (-fromDate.weekday + 1) ..< 1
        var models = deserialize(mac: mac, from: fromDate, range: range)
        
        (0 ..< 7 - models.count).forEach { _ in
            let e = [Element]()
            models.append(e)
        }
        
        return models
    }
    
    /// 查询最近7天的数据（一天一个数组）
    /// - Parameter fromDate: 开始日期
    /// - Returns: [[]]
    static func deserialize(mac: String? = DeviceInfo.current().adress, senvenDays fromDate: Date) ->[[Element]] {
        
        let range = -6 ..< 1
        let models = deserialize(mac: mac, from: fromDate, range: range)
        return models
    }
    
    /// 查询一个月的数据（一天一个数组）
    /// - Parameter fromDate: 开始日期
    /// - Returns: [[]]
    static func deserialize(mac: String? = DeviceInfo.current().adress, month fromDate: Date) ->[[Element]] {
        
        let lastDayDate = fromDate.dateAtEndOf(.month)
        let range = (-lastDayDate.day + 1) ..<  1
        
        let models = deserialize(mac: mac, from: lastDayDate, range: range)
        return models
    }
    
    /// 查询最近30天的数据（一天一个数组）
    /// - Parameter fromDate: 开始日期
    /// - Returns: [[]]
    static func deserialize(mac: String? = DeviceInfo.current().adress, thirtyDays fromDate: Date) ->[[Element]] {
        
        let range =  -29 ..< 1
        let models = deserialize(mac: mac, from: fromDate, range: range)
        return models
    }
    
    /// 查询一年的数据（一天一个数组）
    /// - Parameter fromDate: 一年中随便一个日期
    /// - Returns: [[]]
    static func deserialize(mac: String? = DeviceInfo.current().adress, year fromDate: Date) ->[[Element]] {
        
        let range =  (-fromDate.dayOfYear + 1) ..< 1
        let models = deserialize(mac: mac, from: fromDate, range: range)
        return models
    }
    
    
    /// 查询所有（一天一个数组）
    /// - Returns: [[]]
    static func all(mac: String? = DeviceInfo.current().adress) ->[[Element]] {
        
       
        let query = Element.table//.filter(Element.expression_mac == mac)
        let rows = try? Element.db.prepare(query)
        var models = [Element].deserialize(from: rows)
        
        models = models.sorted {
            
            let time1 = $0.date.timeIntervalSince1970
            let time2 = $1.date.timeIntervalSince1970
            return time1 < time2
            
        }
        
        let subArrayModels = subArray(of: models)
        
        return subArrayModels
        
    }
    
    
    /// 倒序查询所有（一天一个数组）
    /// - Returns: [[]]
    static func descAll(mac: String? = DeviceInfo.current().adress) ->[[Element]] {
        
       
        
        let query = Element.table//.filter(Element.expression_mac == mac)
        let rows = try? Element.db.prepare(query)
        var models = [Element].deserialize(from: rows)
        
        models = models.sorted {
            
            let time1 = $0.date.timeIntervalSince1970
            let time2 = $1.date.timeIntervalSince1970
            return time1 > time2
            
        }
        
        let subArrayModels = subArray(of: models)
        
        return subArrayModels
        
    }
    
    private static func subArray(of array: [Element]) -> [[Element]] {
        
        guard let first = array.first else { return [] }
        
        var allArray: [[Element]] = []
        var subArray: [Element] = []
        
        var beforeDateStr = first.date.toString(.custom("yyyy-MM-dd"))
        array.forEach {
            
            let dateStr = $0.date.toString(.custom("yyyy-MM-dd"))
            if dateStr != beforeDateStr {
                
                allArray.append(Array(subArray))
                subArray.removeAll()
                beforeDateStr = dateStr
                
            }
            
            subArray.append($0)
            
        }
        
        if !subArray.isEmpty {
            allArray.append(subArray)
        }
        
        return allArray
    }
    
    
    
}




